﻿using Dapper;
using Microsoft.AspNetCore.Http.HttpResults;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Reflection.Emit;
using System.Text;
using System.Xml.Linq;
using yunbo.Model.PPMS;

namespace yunbo.Repositorys.PPMSRepositorys
{
    public class Mes_scjhRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";
        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public List<Mes_scjh> GetMes_scjh()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "select* from mes_scjh";
                List<Mes_scjh> Mes_scjh = connection.Query<Mes_scjh>(sql).ToList();
                return Mes_scjh;
            }
        }
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="scjh"></param>
        /// <returns></returns>
        public int AddMesScjh(Mes_scjh scjh)
        {
            const string sql = @"  
INSERT INTO mes_scjh   
    (scjh_state, scjh_yxj, scjh_jd, scjh_code, scjh_lx, scjh_xz,   
     ks_code,ks_name, ht_code, scjh_gdsl, cp_name, cp_code, cp_xh,cp_lx,cp_fl,pp_code,pp_name,    
     dw_code, dw_name, bom_code, scjh_sl, scjh_yscsl, scjh_kssj,   
     scjh_jssj, scjh_xqrq, scjh_bz, scjh_fjzt, scdd_code,   
     create_by, create_time)   
VALUES   
    (@ScjhState, @ScjhYxj, @ScjhJd, @ScjhCode, @ScjhLx, @ScjhXz,   
     @KsCode,@KsName,@HtCode, @ScjhGdsl, @CpName, @CpCode, @CpXh,@CpLX,@CpFL,@PPCODE, @PPNAME,
     @DwCode, @DwName, @BomCode, @ScjhSl, @ScjhYscsl, @ScjhKssj,   
     @ScjhJssj, @ScjhXqrq, @ScjhBz, @SgjhFjzt, @ScddCode,   
     @CreateBy, @CreateTime);";

            try
            {
                using (SqlConnection connection = new SqlConnection(data))
                {
                    connection.Open();
                    var parameters = new
                    {
                        ScjhState = scjh.scjh_state,
                        ScjhYxj = scjh.scjh_yxj,
                        ScjhJd = scjh.scjh_jd,
                        ScjhCode = scjh.scjh_code,
                        ScjhLx = scjh.scjh_lx,
                        ScjhXz = scjh.scjh_xz,
                        KsCode = scjh.ks_code,
                        KsName = scjh.ks_name,
                        HtCode = scjh.ht_code,
                        ScjhGdsl = scjh.scjh_gdsl,
                        CpName = scjh.cp_name,
                        CpCode = scjh.cp_code,
                        CpXh = scjh.cp_xh,
                        CpLX= scjh.cp_lx,
                        CpFL=scjh.cp_fl,
                        PPCODE=scjh.pp_code,
                        PPNAME=scjh.pp_name,
                        DwCode = scjh.dw_code,
                        DwName = scjh.dw_name,
                        BomCode = scjh.bom_code,
                        ScjhSl = scjh.scjh_sl,
                        ScjhYscsl = scjh.scjh_yscsl, 
                        ScjhKssj = scjh.scjh_kssj,
                        ScjhJssj = scjh.scjh_jssj,
                        ScjhXqrq = scjh.scjh_xqrq,
                        ScjhBz = scjh.scjh_bz,
                        SgjhFjzt = scjh.scjh_fjzt,
                        ScddCode = scjh.scdd_code,
                        CreateBy = scjh.create_by,
                        CreateTime = DateTime.Now
                    };

                    var affectedRows = connection.Execute(sql, parameters);
                    return affectedRows;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"添加生产计划失败: {ex.Message}");
                throw;
            }
        }

        public List<int> GetStatus(string scdd_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select scjh_state from mes_scjh where scdd_code='{scdd_code}'";
                List<int> status = connection.Query<int>(sql).ToList();
                return status;
            }
        }
        /// <summary>
        /// 删除生产计划
        /// </summary>
        /// <param name="scdd_code"></param>
        /// <returns></returns>
        public int DeleteScjh(string scdd_code)
        {
            int rowsAffected = 0;
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 修正 SQL 语句
                string sql = $"DELETE FROM mes_scjh WHERE scdd_code = @ScddCode";
                // 使用 Execute 方法执行删除操作
                return rowsAffected = connection.Execute(sql, new { ScddCode = scdd_code });
            }
        }
        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="code"></param>
        /// <param name="state"></param>
        /// <param name="update_by"></param>
        /// <returns></returns>
        public int UpdateStatus(string scdd_code, int state, int update_by,string scjh_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "";
                if (scjh_code == "1")
                {
                    sql = "UPDATE mes_scjh SET scjh_state = @state, update_by = @update_by, update_time = GETDATE() WHERE scdd_code = @scdd_code";
                }
                else
                {
                    sql = "UPDATE mes_scjh SET scjh_state = @state, update_by = @update_by, update_time = GETDATE() WHERE scjh_code = @scjh_code";
                }
                // 使用参数化查询来防止 SQL 注入

                // 创建命令对象并设置参数
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@scjh_code", scjh_code);
                    command.Parameters.AddWithValue("@scdd_code", scdd_code);
                    command.Parameters.AddWithValue("@update_by", update_by);
                    command.Parameters.AddWithValue("@state", state); // 注意：如果 cp_code 是数值类型，则不应该加引号

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }

        /// <summary>
        /// 修改生产计划
        /// </summary>
        /// <param name="scjh"></param>
        /// <returns></returns>
        public int UpdateMesScjh(AddSCJH scjh)
        {
            const string sql = @" UPDATE mes_scjh SET scjh_yxj = @ScjhYxj,scjh_lx = @ScjhLx,scjh_xz = @ScjhXz,ks_code = @KsCode,ks_name = @KsName,cp_name = @CpName,cp_code = @CpCode,cp_xh = @CpXh,cp_lx = @CpLX,cp_fl = @CpFL,pp_code = @PPCODE,dw_code = @DwCode, dw_name = @DwName,bom_code = @BomCode,scjh_sl = @ScjhSl,scjh_kssj = @ScjhKssj,scjh_jssj = @ScjhJssj,scjh_xqrq = @ScjhXqrq,scjh_bz = @ScjhBz,update_by = @CreateBy,update_time = @CreateTime WHERE scjh_code = @ScjhCode;";

                using (SqlConnection connection = new SqlConnection(data))
                {
                    connection.Open();
                    var parameters = new
                    {
                        ScjhYxj = scjh.scjh_yxj,
                        ScjhCode = scjh.scjh_code,
                        ScjhLx = scjh.scjh_lx,
                        ScjhXz = scjh.scjh_xz,
                        KsCode = scjh.ks_code,
                        KsName = scjh.ks_name,
                        CpName = scjh.cp_name,
                        CpCode = scjh.cp_code,
                        CpXh = scjh.cp_xh,
                        CpLX = scjh.cp_lx,
                        CpFL = scjh.cp_fl,
                        PPCODE = scjh.pp_code,
                        DwCode = scjh.dw_code,
                        DwName = scjh.dw_name,
                        BomCode = scjh.bom_code,
                        ScjhSl = scjh.scjh_sl,
                        ScjhKssj = scjh.scjh_kssj,
                        ScjhJssj = scjh.scjh_jssj,
                        ScjhXqrq = scjh.scjh_xqrq,
                        ScjhBz = scjh.scjh_bz,
                        CreateBy = scjh.update_by,
                        CreateTime = scjh.update_time
                    };
                    var affectedRows = connection.Execute(sql, parameters);
                    return affectedRows;
                }
        }


        /// <summary>
        /// 修改工单数量
        /// </summary>
        /// <param name="scjh"></param>
        /// <returns></returns>
        public int UpdateMesScjhgd(int gdsl,string scjhcode)
        {
            const string sql = @" UPDATE mes_scjh SET scjh_gdsl = @Scjhgdsl WHERE scjh_code = @ScjhCode;";

            using (SqlConnection connection = new SqlConnection(data))
            {
                connection.Open();
                var parameters = new
                {
                    Scjhgdsl = gdsl,
                    ScjhCode= scjhcode
                };
                var affectedRows = connection.Execute(sql, parameters);
                return affectedRows;
            }
        }
        public int UpdateMesScjhJD(int jd, string code)
        {
           

            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$" update mes_scjh set scjh_jd=scjh_jd+{jd} where scjh_code='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public Mes_scjh GetJH(string code)
        {


            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"select * from mes_scjh where scjh_code='{code}'";
                Mes_scjh rs = connection.Query<Mes_scjh>(sql).First();
                return rs;
            }
        }
        public List<Mes_scjh> GetJHS(string code)
        {


            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"select * from mes_scjh where scdd_code='{code}'";
                List<Mes_scjh> rs = connection.Query<Mes_scjh>(sql).ToList();
                return rs;
            }
        }
    }
}
